package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.dto.input.BoreholeConditionInputDto;
import com.b511.swpu.cementbackend.dto.output.PageOutputDto;
import com.b511.swpu.cementbackend.dto.output.TestDto;
import com.b511.swpu.cementbackend.entity.Sys_Module;
import com.b511.swpu.cementbackend.model.DataTable;
import com.b511.swpu.cementbackend.model.ModuleDataField;
import com.b511.swpu.cementbackend.util.DBHelper;
import com.b511.swpu.cementbackend.util.Help;
import com.b511.swpu.cementbackend.util.JSONHelper;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.springframework.data.domain.PageRequest;


import javax.validation.constraints.Null;
import java.util.ArrayList;
import java.util.List;

public class DataBrowserRepository {
    public JSONObject findAll(BoreholeConditionInputDto boreholeConditionInputDto, List<ModuleDataField> fields, String moduleName, PageRequest pageRequest) throws Exception {
        DBHelper dbHelper=new DBHelper();

        StringBuilder totalSqlBuilder=new StringBuilder();
        totalSqlBuilder.append("select count(*) ");

        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("select ");
        int count=1;
        for (ModuleDataField field:fields) {
            if(count<fields.size()){
                sqlBuilder.append(" "+field.getTableName()+"."+field.getFieldName()+" ,");
            }
            else {
                sqlBuilder.append(" " + field.getTableName() + "." + field.getFieldName() + " ");
            }
            count++;
        }
        totalSqlBuilder.append(" from BA_WELL ，"+moduleName+"   where BA_WELL.BA_WELL_ID="+moduleName+".BA_WELL_ID ");
        sqlBuilder.append(" from BA_WELL ，"+moduleName+"   where BA_WELL.BA_WELL_ID="+moduleName+".BA_WELL_ID ");
        // sqlBuilder.append("select BOREHOLE.Well_Name,STRATUM.formation,STRATUM.member from BOREHOLE ，"+moduleName+"   where BOREHOLE.id="+moduleName+".borehole_ID ");
        List<Object> params=new ArrayList<Object>();
        if(boreholeConditionInputDto.getWellName()!="" && boreholeConditionInputDto.getWellName() != null){
            sqlBuilder.append(" and BA_WELL.WELLNAME like " + "'%" + boreholeConditionInputDto.getWellName() + "%'");
            totalSqlBuilder.append(" and BA_WELL.WELLNAME like"+ "'%" + boreholeConditionInputDto.getWellName() + "%'");
        }

        if(boreholeConditionInputDto.getWellCategory()!="" && boreholeConditionInputDto.getWellCategory()!= null){
            sqlBuilder.append(" and BA_WELL.WELLCATEGORY=?");
            totalSqlBuilder.append(" and BA_WELL.WELLCATEGORY=?");
            params.add(boreholeConditionInputDto.getWellCategory());
        }


        if(boreholeConditionInputDto.getWellType()!="" && boreholeConditionInputDto.getWellType()!= null){
            sqlBuilder.append(" and BA_WELL.WELLTYPE=?");
            totalSqlBuilder.append(" and BA_WELL.WELLTYPE=?");
            params.add(boreholeConditionInputDto.getWellType());
        }
        // TODO: 判断drillDate
        if(boreholeConditionInputDto.getDrillDate()!="" && boreholeConditionInputDto.getDrillDate()!= null
                && !boreholeConditionInputDto.getDrillDate().equals("undefined&undefined")) {
//            System.out.println("drillDate:" + boreholeConditionInputDto.getDrillDate() );
            String[] date = boreholeConditionInputDto.getDrillDate().split("&");
            sqlBuilder.append(" and BA_WELL.DRILLINGSTARTTIME between date " + "'" + date[0] + "' " + "and date " + "'" + date[1] + "'");
            totalSqlBuilder.append("and BA_WELL.DRILLINGSTARTTIME between date " + "'" + date[0] + "' " + "and date " + "'" + date[1] + "'");
        }

        String sql=sqlBuilder.toString();
        //String sql="select * from Borehole where 1=1 and well_name=? ";

        //查询总记录数total
        String totalSql=totalSqlBuilder.toString();
        int total= dbHelper.executeSingleQuery(totalSql,params);

        int pageSize = pageRequest.getPageSize();
        int page = pageRequest.getPageNumber();
        String data= dbHelper.executeQueryJsonByPage(sql,params, pageSize, page);
//        System.out.println("data=" + data);

        String jsonStr= JSONHelper.getPageJson(data,total,pageSize,page);
//        System.out.println("jsonStr:\n");
//        System.out.println(jsonStr);

        JSONObject jsonobj = new JSONObject();
        JSONArray jarr = JSONArray.fromObject(jsonStr);
        jsonobj.put("data",jarr);
        return jsonobj;
    }

    //查询井
    public JSONObject findWell(BoreholeConditionInputDto boreholeConditionInputDto, PageRequest pageRequest) throws Exception {
        DBHelper dbHelper=new DBHelper();

        StringBuilder totalSqlBuilder=new StringBuilder();
        totalSqlBuilder.append("select count(*) ");


        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("select * ");
        int count=1;

        totalSqlBuilder.append(" from BA_WELL   where 1=1  ");
        sqlBuilder.append(" from BA_WELL   where 1=1  ");
        // sqlBuilder.append("select BOREHOLE.Well_Name,STRATUM.formation,STRATUM.member from BOREHOLE ，"+moduleName+"   where BOREHOLE.id="+moduleName+".borehole_ID ");
        List<Object> params=new ArrayList<Object>();
        if(boreholeConditionInputDto.getWellName()!="" && boreholeConditionInputDto.getWellName() != null){
            sqlBuilder.append(" and BA_WELL.WELLNAME like " + "'%" + boreholeConditionInputDto.getWellName() + "%'");
            totalSqlBuilder.append(" and BA_WELL.WELLNAME like " + "'%" + boreholeConditionInputDto.getWellName() + "%'");
        }

        if(boreholeConditionInputDto.getWellCategory()!="" && boreholeConditionInputDto.getWellCategory()!= null){
            sqlBuilder.append(" and BA_WELL.WELLCATEGORY=?");
            totalSqlBuilder.append(" and BA_WELL.WELLCATEGORY=?");
            params.add(boreholeConditionInputDto.getWellCategory());
        }


        if(boreholeConditionInputDto.getWellType()!="" && boreholeConditionInputDto.getWellType()!= null ){
            sqlBuilder.append(" and BA_WELL.WELLTYPE=?");
            totalSqlBuilder.append(" and BA_WELL.WELLTYPE=?");
            params.add(boreholeConditionInputDto.getWellType());
        }
        // TODO: drillDate between date1 and date2
        // TODO: 判断drillDate
        if(boreholeConditionInputDto.getDrillDate()!="" && boreholeConditionInputDto.getDrillDate()!= null
                && !boreholeConditionInputDto.getDrillDate().equals("undefined&undefined")) {
            String[] date = boreholeConditionInputDto.getDrillDate().split("&");
            sqlBuilder.append(" and BA_WELL.DRILLINGSTARTTIME between date " + "'" + date[0] + "' " + "and date " + "'" + date[1] + "'");
            totalSqlBuilder.append("and BA_WELL.DRILLINGSTARTTIME between date " + "'" + date[0] + "' " + "and date " + "'" + date[1] + "'");
            System.out.println("drillDate:"+date[0] + "----"  + date[1]);
        }

        String sql=sqlBuilder.toString();
        //String sql="select * from Borehole where 1=1 and well_name=? ";

        //查询总记录数total
        String totalSql=totalSqlBuilder.toString();
        int total= dbHelper.executeSingleQuery(totalSql,params);

        int pageSize = pageRequest.getPageSize();
        int page = pageRequest.getPageNumber();
        String data= dbHelper.executeQueryJsonByPage(sql,params, pageSize, page);
        String jsonStr= JSONHelper.getPageJson(data,total,pageSize,page);
//        System.out.println("jsonStr:\n");
//        System.out.println(jsonStr);

        JSONObject jsonobj = new JSONObject();
        JSONArray jarr = JSONArray.fromObject(jsonStr);
        jsonobj.put("data",jarr);
        return jsonobj;
    }
}
